package ar.edu.utn.frgp.servicio;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.Connection;

import ar.edu.utn.frgp.model.Cliente;


public class ClienteService {

	public List<Cliente> getClientes() throws Exception{
		List<Cliente> cli = new ArrayList<Cliente>();
		
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			
			try{
				java.sql.Statement st = conn.createStatement();		
				ResultSet rs = st.executeQuery("SELECT idcliente id, nroDoc doc ,apellido ,nombre FROM clientes");
				while(rs.next()){
					cli.add(new Cliente(rs.getInt("id"), rs.getString("doc"), rs.getString("apellido"), rs.getString("nombre")));
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		} catch (Exception e) {
			throw new Exception(e.toString());
		}
		
		return cli;
	}
	
	public void addCliente(String dni, String apellido, String nombre){
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		
		try{
			Class.forName("com.mysql.jdbc.Driver");			
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			java.sql.Statement st = conn.createStatement();
			st.executeUpdate("INSERT INTO clientes(nroDoc, apellido, nombre) value('" + dni + "', '" + apellido + "', '" + nombre + "')");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public Cliente getCliente(String idCli) throws Exception{
		Cliente  c = new Cliente();
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		try {			
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			
			try{
				java.sql.Statement st = conn.createStatement();		
				ResultSet rs = st.executeQuery("SELECT idcliente id, nroDoc doc ,apellido ,nombre FROM clientes where id  =  '" + idCli + "' ");
			
				while(rs.next()){					
					c = new Cliente(rs.getInt("id"),rs.getString("doc"),  rs.getString("apellido"), rs.getString("nombre"));
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}
		} catch (Exception e) {
			throw new Exception(e.toString());
		}
		
		return c;
	}
	
	
	
	
	
}
